package yun.web.activerecord.dialect.pg;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import com.alibaba.fastjson.JSONObject;
import org.postgresql.util.PGobject;
import yun.web.activerecord.Model;
import yun.web.activerecord.Record;
import yun.web.activerecord.Table;
import yun.web.activerecord.dialect.Dialect;
import yun.web.activerecord.dialect.pg.type.PG;

import java.lang.reflect.Array;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

public class PostgreSqlDialect extends Dialect {

    Log log = LogFactory.get("PostgreSqlDialect");

    public PostgreSqlDialect() {
        this.modelBuilder = new PGModelBuilder();
        this.recordBuilder = new PGRecordBuilder();
    }

    public String forTableBuilderDoBuild(String tableName) {
        return "select * from " + tableName + " where 1 = 2";
    }

    @Override
    public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
        int offset = pageSize * (pageNumber - 1);
        findSql.append(" limit ").append(pageSize).append(" offset ").append(offset);
        return findSql.toString();
    }

    @Override
    public String forModelFindById(Table table, String columns) {
        StringBuilder sql = new StringBuilder("select ");
        sql.append(columns);
        sql.append(" from ");
        //.append('\"')
        sql.append(table.getName());
        //.append('\"')
        sql.append(" where true");

        String[] pKeys = table.getPrimaryKey();
        for (String pKey : pKeys) {
            sql.append(" and ")
                    //.append('\"')
                    .append(pKey)
                    //.append('\"')
                    .append(" = ?");
        }

        return sql.toString();
    }

    @Override
    public String forModelDeleteById(Table table) {
        String[] pKeys = table.getPrimaryKey();
        StringBuilder sql = new StringBuilder(45);
        sql.append("delete from ");
        //sql.append('\"')
        sql.append(table.getName());
        //sql.append('\"')
        sql.append(" where true");
        for (String pKey : pKeys) {
            sql.append(" and ")
                    //.append('\"')
                    .append(pKey)
                    //.append('\"')
                    .append(" = ?");
        }
        return sql.toString();
    }

    @Override
    public void forModelSave(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras, String... generated_keys) {
        String table_name = table.getName();
        List<String> keys = attrs.entrySet().stream().map(it -> {
            String k = it.getKey();
            Object v = it.getValue();
            paras.add(v); // add param
            return k;
        }).collect(Collectors.toList());

        String f1 = CollUtil.join(keys, ",");
        Object[] placeholders = keys.stream().map(it -> "?").toArray();
        String f2 = ArrayUtil.join(placeholders, ",");
        // 拼接sql
        if (keys.size() > 0) {
            sql.append("insert into " + table_name + " (" + f1 + ") values (" + f2 + ")");
        } else {
            sql.append("insert into " + table_name + " default values");
        }

        // 返回字段列表
        if (generated_keys.length > 0) {
            sql.append(" \nreturning ");
            sql.append(ArrayUtil.join(generated_keys, ","));
        }
    }

    @Override
    public void forModelUpdate(Table table, Map<String, Object> attrs, Set<String> modifyFlag, StringBuilder sql, List<Object> paras) {
        sql.append("update ")
                //sql.append('\"')
                .append(table.getName());
        //sql.append('\"')

        String[] pKeys = table.getPrimaryKey();

        attrs.forEach((k, v) -> {
            if (modifyFlag.contains(k) && !isPrimaryKey(k, pKeys) && table.hasColumnLabel(k)) {
                //.append('\"')
                sql.append(" set ")
                        .append(k)
                        //.append('\"')
                        .append(" = ? ");
                paras.add(v);
            }
        });
        sql.append(" where true ");
        for (String pKey : pKeys) {
            sql.append(" and ")
                    //.append('\"')
                    .append(pKey)
                    //.append('\"')
                    .append(" = ?");
            paras.add(attrs.get(pKey));

        }
    }


    @Override
    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List<Object> paras) {
        trimPrimaryKeys(pKeys);

        sql
                .append("update ")
                //.append('\"')
                .append(tableName)
                //.append('\"')
                .append(" set ");
        record.getColumns().entrySet().forEach(it -> {
            String k = it.getKey();
            Object v = it.getValue();
            // 不是主键
            if (!isPrimaryKey(k, pKeys)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                }
                sql
                        //.append('\"')
                        .append(k)
                        //.append('\"')
                        .append(" = ? ");
                paras.add(v);
            }
        });
        sql.append(" where true ");

        for (int i = 0; i < pKeys.length; i++) {
            String pKey = pKeys[i];
            Object pKeyValue = ids[i];
            sql.append(" and ");
            sql
                    //.append('\"')
                    .append(pKey)
                    //.append('\"')
                    .append(" = ?");
            paras.add(pKeyValue);
        }

    }

    @Override
    public void forDbUpsert(String table_name, String[] p_keys, Record record, StringBuilder sql, List<Object> paras, String... generated_keys) {

        List<String> keys = record.getColumns().entrySet().stream().map(it -> {
            String k = it.getKey();
            Object v = it.getValue();
            paras.add(v); // add param
            return k;
        }).collect(Collectors.toList());


        // 拼接sql
        if (keys.size() > 0) {
            String f1 = CollUtil.join(keys, ",");
            Object[] placeholders = keys.stream().map(it -> "?").toArray();
            String f2 = ArrayUtil.join(placeholders, ",");
            sql.append("insert into " + table_name + " (" + f1 + ") values (" + f2 + ")");
        } else {
            sql.append("insert into " + table_name + " default values");
        }

        // upsert处理
        if (paras.size() > 0) {
            String f1 = ArrayUtil.join(p_keys, ",");

            List<String> f2_list = keys.stream().map(it -> it + " = excluded." + it).collect(Collectors.toList());
            String f2 = CollUtil.join(f2_list, ",");

            sql.append("\non conflict (" + f1 + ") do update set " + f2);
        }

        // 返回字段列表
        if (generated_keys.length > 0) {
            sql.append(" \nreturning ");
            sql.append(ArrayUtil.join(generated_keys, ","));
        }
    }

    public String forFindAll(String tableName) {
        return "select * from " + tableName;
    }

    @Override
    public void forModelUpsert(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras, String... generated_keys) {

        List<String> keys = attrs.entrySet().stream().map(it -> {
            String k = it.getKey();
            Object v = it.getValue();
            paras.add(v); // add param
            return k;
        }).collect(Collectors.toList());


        // 拼接sql
        if (keys.size() > 0) {
            String f1 = CollUtil.join(keys, ",");
            Object[] placeholders = keys.stream().map(it -> "?").toArray();
            String f2 = ArrayUtil.join(placeholders, ",");
            sql.append("insert into " + table.getName() + " (" + f1 + ") values (" + f2 + ")");
        } else {
            sql.append("insert into " + table.getName() + " default values");
        }

        // upsert处理
        String[] pks = table.getPrimaryKey();
        if (paras.size() > 0) {
            String f1 = ArrayUtil.join(pks, ",");
            List<String> f2_list = keys.stream().map(it -> it + " = excluded." + it).collect(Collectors.toList());
            String f2 = CollUtil.join(f2_list, ",");
            sql.append("\non conflict (" + f1 + ") do update set " + f2);
        }

        // 返回字段列表
        if (generated_keys.length > 0) {
            sql.append(" \nreturning ");
            sql.append(ArrayUtil.join(generated_keys, ","));
        }

    }

    @Override
    public String forDbFindById(String tableName, String[] pKeys) {
        trimPrimaryKeys(pKeys);
        StringBuilder sql = new StringBuilder("select * from ");
        sql.append(tableName);
        sql.append(" where true ");
        for (String pKey : pKeys) {
            sql.append(" and ");
            sql.append(pKey);
            sql.append(" = ?");
        }
        return sql.toString();
    }

    @Override
    public String forDbDeleteById(String tableName, String[] pKeys) {
        trimPrimaryKeys(pKeys);
        StringBuilder sql = new StringBuilder("delete from ");
        sql.append(tableName);
        sql.append(" where true ");
        for (String pKey : pKeys) {
            sql.append(" and ");
            sql.append(pKey);
            sql.append(" = ?");
        }
        return sql.toString();
    }

    @Override
    public void forDbSave(String tableName, Record record, StringBuilder sql, List<Object> paras, String... generated_keys) {
        String table_name = tableName;
        List<String> keys = record.getColumns().entrySet().stream().map(it -> {
            String k = it.getKey();
            Object v = it.getValue();
            paras.add(v); // add param
            return k;
        }).collect(Collectors.toList());

        String f1 = CollUtil.join(keys, ",");
        Object[] placeholders = keys.stream().map(it -> "?").toArray();
        String f2 = ArrayUtil.join(placeholders, ",");
        // 拼接sql
        if (keys.size() > 0) {
            sql.append("insert into " + table_name + " (" + f1 + ") values (" + f2 + ")");
        } else {
            sql.append("insert into " + table_name + " ()");
        }

        // 返回字段列表
        if (generated_keys.length > 0) {
            sql.append(" \nreturning ");
            sql.append(ArrayUtil.join(generated_keys, ","));
        }
    }

    @Override
    public void getModelGeneratedKey(Model<?> model, PreparedStatement pst, Table table, String... generated_keys) throws SQLException {
        ResultSet rs = pst.getGeneratedKeys();
        if (rs.next()) {
            for (String key : generated_keys) {
                model.set(key, PG.castType(rs.getObject(key)));
            }

        }
        rs.close();
    }


    @Override
    public void getRecordGeneratedKey(PreparedStatement pst, Record record, String[] generated_keys) throws SQLException {
        ResultSet rs = pst.getGeneratedKeys();
        if (rs.next()) {
            for (String key : generated_keys) {
                record.set(key, PG.castType(rs.getObject(key)));
            }

        }
        rs.close();
    }

    /**
     * @author 张天笑
     * @time 2019/1/25 10:18
     * <p>
     * 从 model 或 record 的内置map中读取value并set到pst
     */
    public void fillStatement(PreparedStatement pst, List<Object> paras) throws SQLException {
        int i = 0;
        int size = paras.size();
        while (i < size) {
            i = i(paras, i, pst);
        }
    }

    public void fillStatement(PreparedStatement pst, Object... paras) throws SQLException {
        int i = 0;
        int size = paras.length;
        List list = Arrays.asList(paras);
        while (i < size) {
            i = i(list, i, pst);
        }
    }


    public int i(List paras, int i, PreparedStatement pst) throws SQLException {
        Object value = paras.get(i);
        if (value == null
                || value instanceof String
                || value instanceof BigDecimal
                || value instanceof Long
                || value instanceof Boolean
                || value instanceof Float
                || value instanceof Double
                || value instanceof Integer) {
            pst.setObject(i + 1, value);
        } // 日期
        else if (value instanceof Date) {
            pst.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
        } // 基本类型数组
        else if (value.getClass().isArray()) {
            //获取到当前数组的类型
            String array_type = value.getClass().getTypeName();//.replace("[]", "");
            //获取到对应的Pg类型
            String pg_type = PG.TYPE_MAP.get(array_type);
            //创建对应类型的pg_array对象

            // 蛋疼的数组类型
            int length = Array.getLength(value);
            Object[] os = new Object[length];
            for (int x = 0; x < os.length; x++) {
                Object ai = Array.get(value, x);
                // 单独处理日期, 以免时间戳精度丢失
                if (ai instanceof Date) {
                    os[x] = new Timestamp(((Date) ai).getTime());
                } else {
                    os[x] = ai;
                }
            }
            pst.setArray(i + 1, pst.getConnection().createArrayOf(pg_type, os));
        } else if (value instanceof PGobject) {
            pst.setObject(i + 1, value);
        } else if (value instanceof JSONObject) {
            pst.setObject(i + 1, PG.jsonb(value));
        } else if (value instanceof Record || value instanceof Map) {
            pst.setObject(i + 1, PG.jsonb(value));
        } else {
            log.warn("错误的Java类型:　" + value.getClass().getCanonicalName());
        }
       /*
        if (value == null) {
            pst.setObject(i + 1, null);
        }

        // 常用的类型靠前放
        // 字符串
        else if (value instanceof String) {
            //
            //pst.setObject(i1 + 1, value)
            PGobject pg_object = new PGobject();
            pg_object.setType("text");
            pg_object.setValue((String) value);
            pst.setObject(i + 1, pg_object);

        }
        // 整数
        else if (value instanceof Integer) {
            pst.setInt(i + 1, (int) value);

        }
        //  Long
        else if (value instanceof Long) {
            pst.setLong(i + 1, (Long) value);

        }
        //   BigDecimal
        else if (value instanceof BigDecimal) {
            pst.setBigDecimal(i + 1, (BigDecimal) value);

        }
        // 日期
        else if (value instanceof java.util.Date) {
            pst.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
        }

        // boolean
        else if (value instanceof Boolean) {
            pst.setBoolean(i + 1, (boolean) value);

        } else if (value instanceof Double) {
            pst.setDouble(i + 1, (double) value);
        } else if (value instanceof Float) {
            pst.setFloat(i + 1, (float) value);
        }

        // 基本类型数组
        else if (value.getClass().isArray()) {
            //获取到当前数组的类型
            String array_type = value.getClass().getCanonicalName().replace("[]", "");
            //获取到对应的Pg类型
            String pg_type = TYPE_MAP.get(array_type);
            //创建对应类型的pg_array对象

            // 蛋疼的数组类型
            int length = Array.getLength(value);
            Object[] os = new Object[length];
            for (int x = 0; x < os.length; x++) {
                os[x] = Array.get(value, x);
            }
            pst.setArray(i + 1, pst.getConnection().createArrayOf(pg_type, os));

        }
        // 其他类型
        else if (value instanceof PGobject) {
            pst.setObject(i + 1, value);
        }
        //
        else {
            log.warn("错误的Java类型:　" + value.getClass().getCanonicalName());
        }*/
        return i + 1;
    }



    /*private static String[] javaTypes = {"String", "Integer", "int", "Long", "Date"};
    private static String[] pgTypes = {"text", "int", "int", "bigint", "timestamptz"};

    private String javaTypeToPgType(String type) {
        int idx = ArrayUtil.indexOf(javaTypes, type);
        return pgTypes[idx];
    }*/

}